package com.joker.excel.service;

import com.alibaba.excel.EasyExcel;
import com.alibaba.fastjson.JSON;
import com.joker.excel.bean.bo.ExcelBo;
import com.joker.excel.constants.ExcelConstants;
import com.joker.excel.utils.FileUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import java.io.*;
import java.util.*;

/**
 * @author WangKaiPeng
 * @Title:
 * @Package
 * @Description: 处理excel的service
 * @date 2021/3/250:34
 */
@Service
public class ExcelService {
    private static List<String> ruleList = new ArrayList<>();

    public void loadExcel() {
        return;
    }

    /**
     * <p>
     * 重要规则：
     * 1、HSSF - 读写Microsoft Excel XLS格式 07office以前的版本
     * 2、XSSF - 读写Microsoft Excel OOXML XLSX格式 07office以后的版本
     * </p>
     * <p>
     * 主要逻辑：
     * 1、创建工作簿
     * 2、获取工作表
     * 3、遍历工作表获得行对象
     * 4、遍历行对象获取单元格对象
     * 5、获得单元格的值
     * </p>
     */
    public void calculate(MultipartFile file) {
        File orgFile = null;
        XSSFWorkbook workbook = null;

        try {
            orgFile = FileUtil.multipartFileToFile(file);
            //创建工作簿
            workbook = new XSSFWorkbook(new FileInputStream(orgFile));
            //获取工作表
            XSSFSheet sheet = workbook.getSheetAt(0);
            //遍历工作表
            for (Row cells : sheet) {
                String item = cells.getCell(0).getStringCellValue().split("@")[0];
                if (ExcelConstants.RULE.contains(item)){
                    System.out.println(cells.getRowNum() + "    " + item);
                    int atpSum = getNumericCellValue(cells,1);
                    int intransitSum = getNumericCellValue(cells,2)
                            + getNumericCellValue(cells,3) + getNumericCellValue(cells,4)
                            + getNumericCellValue(cells,5);
                    int openPRSum = getNumericCellValue(cells,6) + getNumericCellValue(cells,7)
                            + getNumericCellValue(cells,8) + getNumericCellValue(cells,9)
                            + getNumericCellValue(cells,10);

                    cells.createCell(11).setCellValue(atpSum);
                    cells.createCell(12).setCellValue(intransitSum);
                    cells.createCell(13).setCellValue(openPRSum);
                }
                //遍历行，获取单元格
//                for (Cell cell : cells) {
//                    //获取单元格的值,判断是数字还是字符串，然后分别输出
//                    if (cell.getCellType() == CellType.NUMERIC) {
//                        System.out.print(cell.getNumericCellValue() + "  ");
//                    } else if (cell.getCellType() == CellType.STRING) {
//                        System.out.print(cell.getStringCellValue() + "  ");
//                    }
//                }
                //每打印一行就换一行
                System.out.println();
            }
            FileOutputStream out = null;
            try {
                System.out.println(orgFile.getAbsolutePath());
                out = new FileOutputStream(orgFile.getAbsolutePath());
                workbook.write(out);
                out.flush();
            } catch (Exception e) {
                e.printStackTrace();
                throw new IllegalStateException("导出文件异常");
            } finally {
                try {
                    if (Objects.nonNull(out)) {
                        out.close();
                    }
                    if (workbook != null) {
                        workbook.close();
                    }
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
//        finally {
//            try {
//                //关闭资源
//                if (workbook != null) {
//                    workbook.close();
//                }
//            } catch (IOException e) {
//                e.printStackTrace();
//            }
//        }

    }

    public void readExcel(MultipartFile file) {
        // 读取全部sheet
        File orgFile = null;
        List<ExcelBo> list = new ArrayList<>();
        try {
            orgFile = FileUtil.multipartFileToFile(file);
            list = EasyExcel.read(orgFile).head(ExcelBo.class).sheet().doReadSync();
        } catch (Exception e) {
            e.printStackTrace();
        }

        for (ExcelBo data : list) {
            System.out.println("读取到数据:{}" + JSON.toJSONString(data));
        }
    }

    private int getNumericCellValue(Row cells,int index) {
        return (int) cells.getCell(index).getNumericCellValue();
    }
}
